from openpyxl import load_workbook
from pathlib import Path
from rich import print as rprint

from sys import path
path.append('d:/python&work')
from little_function import read, convert

ledger_root = Path(r'D:\Desktop\work\10月资料\结算支付台账-2024年10月')
ledger_files = read.get_files(ledger_root)


loan_report_path = ledger_root / '借款报告明细表.xlsx'
wb = load_workbook(loan_report_path)            # 打开借款报告明细表
sheet_names = wb.sheetnames
for the_sheet in sheet_names:
    rprint(f'[bold green]开始处理 {the_sheet}')
    try:
        ledger_file = ledger_files[the_sheet]
    except KeyError:
        rprint(f'[red]{the_sheet} 对应的台账文件不存在，跳过')
        continue
    ledger_content = read.read_ledger(ledger_file)
    ws = wb[the_sheet]
    headers_info = [(cell.column, cell.value) for cell in ws[4]]
    # 删除需要更新的部分
    need_change_columns = ['款项性质', '结算金额（含税）', '应付结算款', '实际已付款', '剩余应付款', '本期计划付款', '备注']
    for the_cell in ws['A']:        # 探索明细表中有多少行明细
        if the_cell.value == '合计':
            last_content_row = the_cell.row - 1
            break
    c_col = ws['C'][4:last_content_row]
    for col_name in need_change_columns:
        try:
            col_index = [t[0] for t in headers_info if t[1] == col_name][0]
        except:
            print(f'{the_sheet} 借款报告明细表 {col_name} 此列需清空，未找到该列')
            continue
        for row_index in range(5, last_content_row + 1):
            ws.cell(row=row_index, column=col_index).value = None
    # 开始填充值
    for col in ['本期计划付款', '备注']:
        need_change_columns.remove(col)        # 剔除需要手动填充的列
    for row_index in range(5, last_content_row + 1):
        for col_name in need_change_columns:
            col = col_index = [t[0] for t in headers_info if t[1] == col_name]
            col_index = col[0]
            unit_col = [t[0] for t in headers_info if t[1] == '往来单位名称'][0]
            the_cell = ws.cell(row=row_index, column=unit_col)
            unit_name = the_cell.value
            if unit_name == None:
                print(f'{the_sheet} 借款报告明细表中往来单位单元格 {the_cell.coordinate} 为空，跳过')
                break
            elif unit_name not in ledger_content:
                print(f'{the_sheet} 借款报告明细表中的 {unit_name} 未在结算支付台账中找到，请检查！')
                break
            the_value = ledger_content[unit_name][col_name]
            try:
                the_value = round(float(the_value), 2)
            except ValueError:
                pass
            ws.cell(row_index, col_index).value = the_value
        else:
            ledger_content.pop(unit_name)
    wb.save(loan_report_path)       # 每个工作表填充完毕后，保存一下
